﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InformationSystem.Framework.Dao;
using InformationSystem.BLL.Core.Model;
using Spring.Data.Generic;
using System.Data;
using Spring.Data.Common;

namespace InformationSystem.BLL.Core.Dao.Impl
{
    public class UserDaoImpl : InfoSysAdoDaoSupport, IUserDao
    {
        public LoginUser Login(string userid, string stu, string loginpwd)
        {
            string selectSql = @"select userid,username,isSystemAdmin,Cast(0 as bit) as loginDeny,
		                                case when isSystemAdmin=1 then 1 else 2 end as UserType,collegeid,gradeid,lastLogindatetime 
                                from 
	                                SystemUser 
                                where userid=@userID and loginpwd=@loginpwd
                                union all select 
	                                @userID as userid,username,Cast(0 as bit) as isSystemAdmin,loginDeny,case when userid=@userID then 4 else 8 end as UserType,collegeid,gradeid,case when userid=@userID then lastLogindatetime else plastLogindatetime end as lastLogindatetime 
                                from StudentUser  
                                where (userid=@userID and loginpwd=@loginpwd and @userID=@stu) or (userid=@stu and ppwd=@loginpwd and @userID<>@stu )";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userID").Type(DbType.String).Value(userid);
            builder.Create().Name("stu").Type(DbType.String).Value(stu);
            builder.Create().Name("loginpwd").Type(DbType.String).Value(loginpwd);
            IList<LoginUser> userlist = AdoTemplate.QueryWithRowMapper<LoginUser>(CommandType.Text, selectSql, new UserRow<LoginUser>(), builder.GetParameters());
            if (userlist.Count == 0)
            {
                return null;
            }
            else
            {
                return userlist[0];
            }
        }

        public void UpdateLoginInfo(LoginUser user, SysEnvironment envir)
        {
            //更新最后一次登陆时间
            string updateSql = "update {0} set {1}=@lastLogindatetime where userid=@userid";
            if (user.LoginUserType == UserType.Admin || user.LoginUserType == UserType.Assistant)
            {
                updateSql = string.Format(updateSql, "SystemUser", "lastLogindatetime");
            }
            else
            {
                updateSql = string.Format(updateSql, "StudentUser", user.LoginUserType == UserType.Parent ? "plastLogindatetime" : "lastLogindatetime");
            }
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userid").Type(DbType.String).Value(user.UserID);
            builder.Create().Name("lastLogindatetime").Type(DbType.DateTime).Value(DateTime.Now);
            AdoTemplate.ExecuteNonQuery(CommandType.Text, updateSql, builder.GetParameters());
            //TODO保存登陆记录
        }


        public List<LoginUser> GetSysUser()
        {
            string selectSql = @"select 
	                                userid,username,isSystemAdmin,SystemUser.collegeid,College.collegeName,gradeid
                                from 
	                                SystemUser left join College 
                                on 
	                                SystemUser.collegeid=College.collegeid order by userid";
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreate(CommandType.Text, selectSql);
            if (dt == null || dt.Rows.Count == 0) return null;
            List<LoginUser> userList = new List<LoginUser>();
            foreach (DataRow item in dt.Rows)
            {
                LoginUser lu = new LoginUser();
                lu.UserID = item["userid"].ToString();
                lu.UserName = item["username"].ToString();
                lu.IsSystemAdmin = (bool)item["isSystemAdmin"];
                lu.CollegeID = (int)item["collegeid"];
                lu.CollegeName = item["collegeName"].ToString();
                lu.GradeID = (int)item["gradeid"];
                userList.Add(lu);
            }
            return userList;
        }

        public void LogOut(LoginUser user)
        {

        }

        internal class UserRow<T> : IRowMapper<T> where T : LoginUser, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T user = new T();
                user.UserID = reader.GetString(reader.GetOrdinal("userid"));
                user.UserName = reader.GetString(reader.GetOrdinal("username"));
                user.IsSystemAdmin = reader.GetBoolean(reader.GetOrdinal("isSystemAdmin"));
                user.LoginDeny = reader.GetBoolean(reader.GetOrdinal("loginDeny"));
                user.LoginUserType = (UserType)reader.GetInt32(reader.GetOrdinal("UserType"));
                user.CollegeID = reader.GetInt32(reader.GetOrdinal("collegeid"));
                user.GradeID = reader.GetInt32(reader.GetOrdinal("gradeid"));
                user.LastLoginDateTime = reader.IsDBNull(reader.GetOrdinal("lastLogindatetime")) ? DateTime.Now : reader.GetDateTime(reader.GetOrdinal("lastLogindatetime"));
                return user;
            }
        }


        public bool ChangeSysUserPassword(string userid, string oldpwd, string newpwd)
        {
            string updateSql = @"update SystemUser set loginpwd=@newpwd where userid=@userid and loginpwd=@oldpwd";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("newpwd").Type(DbType.String).Value(newpwd);
            builder.Create().Name("userid").Type(DbType.String).Value(userid);
            builder.Create().Name("oldpwd").Type(DbType.String).Value(oldpwd);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, updateSql, builder.GetParameters()) == 1;
        }

        public bool ChangeStudentPassword(LoginUser user, string oldpwd, string newpwd)
        {
            string updatestuSql = @"update StudentUser set loginpwd=@newpwd where userid=@userid and loginpwd=@oldpwd";
            string updateparSql = @"update StudentUser set ppwd=@newpwd where userid=@userid and ppwd=@oldpwd";

            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("newpwd").Type(DbType.String).Value(newpwd);
            builder.Create().Name("userid").Type(DbType.String).Value(user.LoginUserType == UserType.Parent ? user.StudentID : user.UserID);
            builder.Create().Name("oldpwd").Type(DbType.String).Value(oldpwd);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, user.LoginUserType == UserType.Parent ? updateparSql : updatestuSql, builder.GetParameters()) == 1;
        }


        public bool UpdateSysUserInfo(LoginUser user)
        {
            string updateSql = "update SystemUser set username=@username,isSystemAdmin=@isSystemAdmin,collegeid=@collegeid,gradeid=@gradeid where userid=@userid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("username").Type(DbType.String).Value(user.UserName);
            builder.Create().Name("isSystemAdmin").Type(DbType.Boolean).Value(user.IsSystemAdmin);
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(user.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(user.GradeID);
            builder.Create().Name("userid").Type(DbType.String).Value(user.UserID);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, updateSql, builder.GetParameters()) == 1;
        }

        public bool CreateSysUserInfo(LoginUser user)
        {
            string selectSql = "select count(userid) as idcount from SystemUser where userid=@userid";
            string insertSql = "insert into  SystemUser (userid,username,loginpwd,isSystemAdmin,collegeid,gradeid) values(@userid,@username,@loginpwd,@isSystemAdmin,@collegeid,@gradeid)";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userid").Type(DbType.String).Value(user.UserID);

            int count = (int)AdoTemplate.ClassicAdoTemplate.ExecuteScalar(CommandType.Text, selectSql, builder.GetParameters());
            if (count != 0)
            {
                user.InsertOK = false;
                user.Msg = "存在相同记录!";
                return false;
            }
            builder.Create().Name("username").Type(DbType.String).Value(user.UserName);
            builder.Create().Name("loginpwd").Type(DbType.String).Value(user.LoginPwd);
            builder.Create().Name("isSystemAdmin").Type(DbType.Boolean).Value(user.IsSystemAdmin);
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(user.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(user.GradeID);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, insertSql, builder.GetParameters()) == 1;
        }


        public bool DeleteSysUserByID(string userID)
        {
            string deleteSql = "delete from SystemUser where userid=@userid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userid").Type(DbType.String).Value(userID);
            return AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, builder.GetParameters()) == 1;
        }


        public DataTable GetUserContact()
        {
            string selectSql = @"select username,userid,
                                case when isSystemAdmin=1 then '管理员' else Cast(gradeid as nvarchar(4)) + '级辅导员' end as persondesp from SystemUser 
                                union all
                                select username,userid ,'学生' as persondesp from StudentUser
                                union all
                                select username+'家长' as username,'jz'+userid as userid,'家长' as persondesp from StudentUser
                                order by username";
            return AdoTemplate.ClassicAdoTemplate.DataTableCreate(CommandType.Text, selectSql);
        }


        public bool CheckUserExisted(string userid)
        {
            string selectSql = @"select userid from SystemUser where userid=@userid
                                union all
                                select userid from StudentUser where userid=@userid or userid=@userid2";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("userid").Type(DbType.String).Value(userid);
            builder.Create().Name("userid2").Type(DbType.String).Value(userid.TrimStart(new char[] { 'j', 'z' }));
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            return dt.Rows.Count == 0 ? false : true;
        }


        public IList<string> GetCollegeAssistant(LoginUser user)
        {
            string selectSql = "select  username from SystemUser where collegeid=@collegeid and gradeid=@gradeid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(user.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(user.GradeID);
            DataTable dt = AdoTemplate.ClassicAdoTemplate.DataTableCreateWithParams(CommandType.Text, selectSql, builder.GetParameters());
            IList<string> userlist = new List<string>();
            foreach (DataRow item in dt.Rows)
            {
                userlist.Add(item["username"].ToString());
            }
            return userlist;

        }

        public int GetAllStudentCount()
        {
            string selectSql = "select count(*) from StudentUser";
            return (int)AdoTemplate.ExecuteScalar(CommandType.Text, selectSql);
        }

        public int GetStudentCountInGrade(LoginUser user)
        {
            string selectSql = "select count(*) from StudentUser where collegeid=@collegeid and gradeid=@gradeid";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("collegeid").Type(DbType.Int32).Value(user.CollegeID);
            builder.Create().Name("gradeid").Type(DbType.Int32).Value(user.GradeID);
            return (int)AdoTemplate.ExecuteScalar(CommandType.Text, selectSql, builder.GetParameters());
        }

        public int GetAllAssistantCount()
        {
            string selectSql = "select count(*) from SystemUser where isSystemAdmin=0";
            return (int)AdoTemplate.ExecuteScalar(CommandType.Text, selectSql);
        }

        internal class SearchResultRow<T> : IRowMapper<T> where T : SearchResult, new()
        {
            public T MapRow(IDataReader reader, int rowNum)
            {
                T searchresult = new T();
                searchresult.UserID = reader.GetString(reader.GetOrdinal("userid"));
                searchresult.UserName = reader.GetString(reader.GetOrdinal("username"));
                searchresult.Descp = reader.GetString(reader.GetOrdinal("persondesp"));
                return searchresult;
            }
        }

        public IList<SearchResult> GetUserContactWithFilter(string filter)
        {
            string selectSql = @"select username,userid,
                                case when isSystemAdmin=1 then '管理员' else Cast(gradeid as nvarchar(4)) + '级辅导员' end as persondesp from SystemUser 
                                where username like @filter or userid like  @filter
                                union all
                                select username,userid ,'学生' as persondesp from StudentUser
                                where username like @filter or userid like  @filter
                                union all
                                select username+'家长' as username,'jz'+userid as userid,'家长' as persondesp from StudentUser
                                where username like @filter or userid like  @filter
                                order by username";
            IDbParametersBuilder builder = CreateDbParametersBuilder();
            builder.Create().Name("filter").Type(DbType.String).Value(string.Format("%{0}%", filter));
            return AdoTemplate.QueryWithRowMapper<SearchResult>(CommandType.Text, selectSql, new SearchResultRow<SearchResult>(), builder.GetParameters());

        }
    }
}
